package com.mengtian_tech.tju.dataquery.api.service;

import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mengtian_tech.tju.dataquery.api.model.tjuOracle.College;
import com.mengtian_tech.tju.dataquery.api.model.tjuOracle.Employee;
import com.mengtian_tech.tju.dataquery.api.repo.tjuOracle.EmployeeRepo;


@Service
public class EmployeeService {
	
	@Autowired
	@Qualifier("jdbcTemplateThimis")
	JdbcTemplate jdbcTemplateThimis;
	
	@Autowired
	EmployeeRepo employeeRepo;
	
	 
	
	public List<Map<String,Object>> findById(String staffId) {
		List<Map<String,Object>> list =  this.jdbcTemplateThimis.queryForList("select * from app_manager.employee where staff_id = ?",staffId);
		return list;
	}
	
	 
	public String findNameById(String staffId) {
		String name = null;
		List<Map<String,Object>> list =  this.jdbcTemplateThimis.queryForList("select name from app_manager.employee where staff_id = ?",staffId);
		if(list.size() > 0) {
			name = (String) list.get(0).get("name");
		}
		return name;
	}
	
	
	public College getCollege(String staffId) {
		List<Map<String,Object>> list = this.jdbcTemplateThimis.queryForList(
				"select t0.college_id,t0.college from app_manager.dict_college t0, app_manager.employee t1 where t1.staff_id = ? and t1.college_id = t0.college_id",
				staffId);
		
		if(list.size() > 0) {
			Map<String, Object> map  = list.get(0);
			College college = new College();
			college.setCollegeId(map.get("college_id").toString());
			college.setCollegeName(map.get("college").toString());
			return college;
		}
		
		return null;
		
	}


	public Employee findByStaffId(String id) {
		return this.employeeRepo.findById(id).orElse(null);
	}


	public List<Map<String,Object>> getPageList(Integer page, Integer limit, String staffName, String staffId) {
		
		if(StringUtils.isEmpty(staffId)) staffId = "";
		if(StringUtils.isEmpty(staffName)) staffName = "";		
		
		String sql="select t0.college_id,t0.college , t1.name,t1.staff_id  from app_manager.dict_college t0, app_manager.employee t1 where t1.college_id = t0.college_id ";
		
		sql += " and  t1.name like ?  and t1.staff_id like ?  ";
		
		String sql1 = "  SELECT * FROM ("
				+ "SELECT t.*, ROWNUM rn FROM ("
				+ sql
				+ ") t WHERE ROWNUM <= ?"
				+ ") WHERE rn > ? ";
		
		return this.jdbcTemplateThimis.queryForList(sql1,"%"+staffName+"%","%"+staffId+"%", (page*limit),(page-1)*limit);
		
	}
	
}
